 /*
  *@(#)SpiderDBConnectionImpl.java	20 Sept. 2000
  *
  * Copyright: MetaPlus Inc & Inquiry Management Systems Ltd.  2000
  *
  * This software is the proprietary information of MetaPlus Inc & 
  * Inquiry Management Systems Ltd.  
  * Use is subject to license terms.
  */
package com.ims.iaa;

import java.io.*; 
import java.util.*; 
import java.sql.*;
import java.net.URL;
import java.rmi.*;

import com.ims.iaa.AD.*;
import com.ims.iaa.Cache.IAppLvl;

/** 
  *
  * <p>This class <code>SpiderDBConnectionImpl</code> provides the 
database 
  * services for other classes. This class is act as an remote RMI 
services 
  * object which implements the <code>SpiderDBConnection</code> remote 
  * interface.</p>
  * <p>JDBC driver and connection string are required for this class to 
function.
  * Properties include db.userName, db.password, db.driverName,db.url 
should be
  * save in a property file with name db.properties under the security 
  * sub-directory. A sample file is shipped with the program.</p>
  * <p>Note, for some JDBC driver there might be incompatible problems, 
e.g. the 
  * data type Timestamp or Datetime can not be saved correctly. One 
possible 
  * solution is use SQL Server internal function GETDATE() or 
CURRENT_TIMESTAMP.
  * </p>
  * Add storeFlashLink() by ShengMeng Bai at 2004-07-24
  * Add loadAgency() by ShengMeng Bai at 2004-07-28
  *
  * @see SpiderDBConnection
  *
  * @author Anthony Zhang
  * @author ShengMeng Bai
  *
  * @version 1.00, 25 Sept. 2000
  * @version 2.00, 24 JUL. 2004
  *
  */
public class SpiderDBConnectionImpl 
	extends java.rmi.server.UnicastRemoteObject implements 
SpiderDBConnection {

	/**
	  * The database connection used by other method
	  */
	/*private Connection  cn;	*/
	private static SpiderDBConnection sp;
	//added by Anthony at 18/DEC/2000 this count record the impression collected 
	// the server since the database connection opened.
	private int impCollected = 0;
	//added by Anthony at 18/DEC/2000 this count determine the max impression number 
	// allowed per database connection. if impression number exceed this value, a new
	//database connection should be opened.
	public static final int MaxImpPerConnection = 50000;
		
	static ManualPoolingDataSource pool = null ;
	private SpiderDBConnectionImpl() throws java.rmi.RemoteException {
	    pool = ManualPoolingDataSource.getInstance() ;
	}
    /**
      * Get the only instance of <code>SpiderDBConnectionImpl</code>. 
This
	  * method is used to support the singleton feture of this class.
	  *
      * @return A <code>SpiderDBConnection</code> object which actually 
is
	  * the only instance of <code>SpiderDBConnectionImpl</code> class.
      *
      */
	static public SpiderDBConnection getInstance()  {		
	    if (sp == null)  {
			try  {
				sp = new SpiderDBConnectionImpl();
			} catch ( java.rmi.RemoteException e)  {
				IaaUtil.log("Create Database connection failed " + e);
			    IaaUtil.printStackTrace(e);
			}
		}
		return sp;
	}
	/**
	 * 
	 */
	public int storeAdDataLink( AdDataLink objAdDataLink, LocalRequest lReq, String sThreadName) throws RemoteException {
	    Connection cn = null ;
	    try{
	        cn=pool.getConnection();
		    //++changsheng 2006-5-18
			ResultSet rs = null ;
			int thisImpressionID = 0 ;
	//--
			//int iImageId = 0;
		    int iImageId = -1;
			//String spiderName =	lReq.getSpiderName() + ": ";
			try  {
			    if( !(objAdDataLink instanceof HrefLink) ){//++changsheng gong  
					iImageId = storeAdData(objAdDataLink.getAdData(), sThreadName,cn);
			    }
			} catch (RemoteException e)  {
				IaaUtil.log(sThreadName + "Store AdData error " + e);
			    IaaUtil.printStackTrace(e);
			}
				
			int iFlashSiteId = getSiteIdFromDB( objAdDataLink.getAdDataSite(), sThreadName , cn);
			int iPublisherId = getCompanyIdFromDB( objAdDataLink.getPublisher(), sThreadName,cn);
			//flashLink.getAdvertiser() may be null
			String sAdvertise = objAdDataLink.getAdvertiser();
			int iAdvertiserId = -1;
			if ( sAdvertise != null ){
				iAdvertiserId = getCompanyIdFromDB( objAdDataLink.getAdvertiser(), sThreadName,cn);
			}
				
			if(IaaDebug.DatabaseDebug) 
				IaaUtil.log( sThreadName + "Finish get site,company data" );		
		
			PreparedStatement objPrStmt = null;
			try  {		
				objPrStmt = cn.prepareStatement("insert into tblImpression " +
					"(intImageID,intPublisherID,strPublisherURL,intImageSiteID," +
					"strIMGTargetURL,strTitle, intImpressionHeight,intImpressionWidth," +
					"strLocation,intSearchID,intAdvertiserID, strAdvertisementType, " +
					"strAdvertisementTarget,strImageURL,intCriteria,intAgencyID," +
					"strPopupParent,strAppletParam,bitState,dteCollectTime,impressionDepth)" + 
					" Values (?,?,?,?, ?,?,?,?, ?,?,?,?, ?,?,?,?,?,?,?,CURRENT_TIMESTAMP,?)");
		
				//objPrStmt.setInt( 1, iImageId );
				//modified by changsheng gong
				if( iImageId != -1){
				    objPrStmt.setInt( 1,iImageId );
				}else{
				    objPrStmt.setNull(1, java.sql.Types.INTEGER);
				}
				
				objPrStmt.setInt( 2, iPublisherId );					 
	
				String sPubUrl = objAdDataLink.getPublisherURL().toString();
	
				objPrStmt.setString( 3, sPubUrl);
	
				objPrStmt.setInt(4,iFlashSiteId);		 
	
				String sStaticTarget = objAdDataLink.getTargetURLStr();
				String sRealTarget = objAdDataLink.getRealTargetURLStr();
				//++ change by ShengMengBai at 2005-04-30
				//if the target equal realtarget, don't save the target
				if(sStaticTarget != null && sRealTarget != null && sRealTarget.equals(sStaticTarget)){
				    objPrStmt.setString(5, "");
				}else{
				    objPrStmt.setString(5, sStaticTarget);
				}
				//-- change by ShengMengBai at 2005-04-30
				String sTitle = objAdDataLink.getAdTitle();
				objPrStmt.setString(6, sTitle);		
	
				objPrStmt.setInt( 7, sizeToSave(objAdDataLink.getHeight()));		 
				objPrStmt.setInt( 8, sizeToSave(objAdDataLink.getWidth()));		 
				objPrStmt.setString( 9, objAdDataLink.getLocation() );		 
				objPrStmt.setInt( 10,lReq.getSearchId() );
				if ( iAdvertiserId != -1 ){
					objPrStmt.setInt( 11,iAdvertiserId );
				} else {
					objPrStmt.setNull(11, java.sql.Types.INTEGER);
				}
				objPrStmt.setString( 12, objAdDataLink.getAdType() );
	
				//String sRealTarget = objAdDataLink.getRealTargetURLStr();
				//if(realTarget.length()>255)realTarget = realTarget.substring(0,255);		 		 
				objPrStmt.setString( 13, sRealTarget); 
	
				String sSrcUrl = objAdDataLink.getSrcURLStr();
				//if(srcUrl.length()>255)srcUrl = srcUrl.substring(0,255);		 
				objPrStmt.setString( 14, sSrcUrl);
				
				objPrStmt.setInt( 15, objAdDataLink.getPossibility());
				
				if (objAdDataLink.getAdAgency()!= null){
					objPrStmt.setInt(16, objAdDataLink.getAdAgency().getAgencyID());
				} else {
					objPrStmt.setNull(16, java.sql.Types.INTEGER);
				}
				objPrStmt.setString( 17, objAdDataLink.getPopupParentURLStr());
				objPrStmt.setString( 18, objAdDataLink.getAppletParamStr());
				if(objAdDataLink.isConnectSuccessful()){
					objPrStmt.setInt(19, 1);
				}else{
					objPrStmt.setInt(19, 0);
				}
	
				objPrStmt.setInt(20,objAdDataLink.getDepth() );//changsheng for depth
				
				//++modified by changsheng 2006-5-18
				//return the impression id
				//objPrStmt.executeUpdate();
				if( objPrStmt.executeUpdate() != 0 )
				{
					objPrStmt = cn.prepareStatement("SELECT @@IDENTITY as ID FROM tblImpression");
				    rs = objPrStmt.executeQuery() ;
				    rs.next() ;
				    thisImpressionID = rs.getInt("ID");
				}
				//--
			} catch (SQLException e)  {
			    reGetConnection();
				IaaUtil.log(sThreadName + "SQL7 Error tblImpression " + e);
			    IaaUtil.printStackTrace(e);
			}		
			finally{
				try{
					if(rs != null ){rs.close();}
				    if(objPrStmt != null){
				        objPrStmt.close();
				    }
				}catch(SQLException e){};
			}
			if(IaaDebug.DatabaseDebug) 
				IaaUtil.log( sThreadName + "Finish store AdDataLink object");	
			return thisImpressionID ;
	    }catch(Exception e){
	        IaaUtil.printStackTrace(e);
	    }finally {
            try {
                if (cn != null)
                    cn.close();
            } catch (SQLException e) {
            }
        }

	    return -1 ;
	}
	
	/*public synchronized int getCompanyIdFromDB(String url, String sThreadName) throws RemoteException {*/
    private int getCompanyIdFromDB(String url, String sThreadName,Connection cn){
		
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		
		int companyId = -1;
		try  {
			pStmt = cn.prepareStatement("SELECT intCompanyID from  tblCompany" + 
								" where strCompanyURL = ?"); 
			pStmt.setString( 1, url);
			rs = pStmt.executeQuery();
			if (rs.next())  {
				companyId = rs.getInt(1);
			} 
		} catch (SQLException e)  {
			IaaUtil.log(sThreadName + "SQL7 Error tblCompany select " + e);
		    IaaUtil.printStackTrace(e);
			return companyId;
		}
		finally{
			try{
			    if(rs != null){
			        rs.close();
			        rs = null;
			    }
			    if(pStmt != null){
			        pStmt.close();
			        pStmt = null;
			    }
			}catch(SQLException e){};
		}
		
		if (companyId == -1)  {
			try  {
				pStmt = cn.prepareStatement(
					"insert into  tblCompany (strCompanyURL) Values (?)");
				pStmt.setString( 1, url);
				pStmt.executeUpdate();
			} catch (SQLException e)  {
			    reGetConnection();
				IaaUtil.log(sThreadName + "SQL7 Error tblCompany insert " + e);
			    IaaUtil.printStackTrace(e);
				return companyId;		
			}
			finally{
				try{
				    if(pStmt != null){
				        pStmt.close();
				    }
				}catch(SQLException e){};
			}
			
			Statement stmt = null;
			try  {
				stmt= cn.createStatement();
				rs = stmt.executeQuery("SELECT @@IDENTITY");
				if (rs.next())  {
					companyId = rs.getInt(1);
				} 
			} catch (SQLException e)  {
			    reGetConnection();
				IaaUtil.log(sThreadName + "SQL7 cannot get the company id " + e);
			    IaaUtil.printStackTrace(e);
				return companyId;
			}			
			finally{
				try{
				    if(rs != null){
				        rs.close();
				    }
				    if(stmt != null){
				        stmt.close();
				    }
				}catch(SQLException e){};
			}			
		}
		if (IaaDebug.DatabaseDebug) 
			IaaUtil.log(sThreadName + "Get company id " + companyId + " by Company URL: " + url);		
		return companyId;
	}

	/*public synchronized int getSiteIdFromDB(String url, String sThreadName) throws RemoteException {*/
	private int getSiteIdFromDB(String url, String sThreadName,Connection cn){
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		
		int siteId = -1;
		try  {
			pStmt = cn.prepareStatement("SELECT intSiteID from  tblSite" + 
								" where strSiteURL = ?"); 
			pStmt.setString( 1, url);
			rs = pStmt.executeQuery();
			if (rs.next())  {
				siteId = rs.getInt(1);
			} 
		} catch (SQLException e)  {
		    reGetConnection();
			IaaUtil.log(sThreadName + "SQL7 Error tblSite select " + e);
		    IaaUtil.printStackTrace(e);
			return siteId;
		}
		finally{
			try{
			    if(rs != null){
			        rs.close();
			        rs = null;
			    }
			    if(pStmt != null){
			        pStmt.close();
			        pStmt = null;
			    }
			}catch(SQLException e){};
		}			
		
		if (siteId == -1)  {
			try  {
				pStmt = cn.prepareStatement(
					"insert into  tblSite (strSiteURL) Values (?)");
				pStmt.setString( 1, url);
				pStmt.executeUpdate();
			} catch (SQLException e)  {
				IaaUtil.log(sThreadName + "SQL7 Error tblSite insert " + e);
			    IaaUtil.printStackTrace(e);
				try  {
					cn.rollback();
				} catch (SQLException ex)  {
				    reGetConnection();
					IaaUtil.log(sThreadName + "Rollback " + ex);					
				    IaaUtil.printStackTrace(e);
				}
				return siteId;		
			}
			finally{
				try{
				    if(pStmt != null){
				        pStmt.close();
				    }
				}catch(SQLException e){};
			}			
			
			Statement stmt = null;
			try  {
				stmt= cn.createStatement();
				rs = stmt.executeQuery("SELECT @@IDENTITY");
				if (rs.next())  {
					siteId = rs.getInt(1);
				} 
			} catch (SQLException e)  {
			    reGetConnection();
				IaaUtil.log(sThreadName + "SQL7 cannot get the site id " + e);
			    IaaUtil.printStackTrace(e);
				return siteId;
			}			
			finally{
				try{
				    if(rs != null){
				        rs.close();
				    }
				    if(stmt != null){
				        stmt.close();
				    }
				}catch(SQLException e){};
			}			
			
		}
		if (IaaDebug.DatabaseDebug) 
			IaaUtil.log(sThreadName + "Get site id " + siteId + " by Site URL: " + url);		
		return siteId;
	}
	
	public  int getRequestIdFromDB(LocalRequest lReq, String sThreadName)  
			throws RemoteException {
	    Connection cn = null ;
	    try{
	        cn=pool.getConnection();
			PreparedStatement pStmt = null;
			ResultSet rs = null;

			int customerId = lReq.getCustomerId();
			String site = lReq.getUrl();
			int depth = lReq.getDepth();
			String method = lReq.getSamplingMethod();
			int time = lReq.getMaxRunningTime();
	
			int reqId = -1;
			try  {
				pStmt = cn.prepareStatement("SELECT intRequestID from  tblRequest" + 
					" where intCustomerID = ? and strSiteURL = ? and  intSearchDepth " +
					" = ? and strSamplingMethod = ? and intMaxRunningTime = ? " );
				pStmt.setInt( 1, customerId);
				pStmt.setString( 2, site);
				pStmt.setInt( 3, depth);
				pStmt.setString( 4, method);					
				pStmt.setInt( 5, time);	

				rs = pStmt.executeQuery();
				if (rs.next())  {
					reqId = rs.getInt(1);
				} 
			} catch (SQLException e)  {
			    reGetConnection();
				IaaUtil.log(sThreadName + "SQL7 Error tblRequest select" + e);
			    IaaUtil.printStackTrace(e);
				return reqId;
			}
			finally{
				try{
				    if(rs != null){
				        rs.close();
				        rs = null;
				    }
				    if(pStmt != null){
				        pStmt.close();
				        pStmt = null;
				    }
				}catch(SQLException e){};
			}			
		
			if (reqId == -1)  {
				try  {
					pStmt = cn.prepareStatement("insert into  tblRequest " + 
						"(intCustomerID,strSiteURL,intSearchDepth,strSamplingMethod," +
						"intMaxRunningTime,bitLoadFlag) Values (?,?,?,?,?,?)");
					pStmt.setInt( 1, customerId);
					pStmt.setString( 2, site);
					pStmt.setInt( 3, depth);
					pStmt.setString( 4, method);					
					pStmt.setInt( 5, time);	
					pStmt.setByte( 6, (byte)1);
	
					pStmt.executeUpdate();
				} catch (SQLException e)  {
				    reGetConnection();
					IaaUtil.log(sThreadName + "SQL7 Error tblRequest insert " + e);
				    IaaUtil.printStackTrace(e);
					return reqId;		
				}
				finally{
					try{
					    if(pStmt != null){
					        pStmt.close();
					    }
					}catch(SQLException e){};
				}
				Statement stmt = null;
				try  {
					stmt= cn.createStatement();
					rs = stmt.executeQuery("SELECT @@IDENTITY");
					if (rs.next())  {
						reqId = rs.getInt(1);
					} 
				} catch (SQLException e)  {
				    reGetConnection();
					IaaUtil.log(sThreadName + "SQL7 cannot get the request id " + e);
				    IaaUtil.printStackTrace(e);
					return reqId;
				}			
				finally{
					try{
					    if(rs != null){
					        rs.close();
					    }
					    if(stmt != null){
					        stmt.close();
					    }
					}catch(SQLException e){};
				}			
			
			}
			if (IaaDebug.DatabaseDebug) 
				IaaUtil.log(sThreadName + "Get request id " + reqId + " by URL: " +lReq.getUrl() 
						+ " and DEPTH: " + lReq.getDepth());
			return reqId;
	    }catch(Exception e){
	        IaaUtil.printStackTrace(e);
	    }finally {
            try {
                if (cn != null)
                    cn.close();
            } catch (SQLException e) {
            }
        }
	    return -1 ;
	}
	
	public  int getSearchIdFromDB(LocalRequest lReq, String sThreadName) 
		throws RemoteException {
	    Connection cn = null ;
	    try{
	        cn=pool.getConnection();
			PreparedStatement pStmt = null;
			ResultSet rs = null;
				
			int searchId = -1;
			try  {
				pStmt = cn.prepareStatement("insert into  tblSearch " +
								"(intRequestID) Values (?)"); 
				
				pStmt.setInt(1,lReq.getRequestId());
	
				pStmt.executeUpdate();
			} catch (SQLException e)  {
			    reGetConnection();
				IaaUtil.log(sThreadName + "SQL7 Error tblSearch " + e);
			    IaaUtil.printStackTrace(e);
				return searchId;
			} 
			finally{
				try{
				    if(pStmt != null){
				        pStmt.close();
				    }
				}catch(SQLException e){};
			}
			
			Statement stmt = null;
			try  {
				stmt= cn.createStatement();
				rs = stmt.executeQuery("SELECT @@IDENTITY");
				if (rs.next())  {
					searchId = rs.getInt(1);
				} 
			} catch (SQLException e)  {
			    reGetConnection();
				IaaUtil.log(sThreadName + "SQL7 cannot get the search id " + e);
			    IaaUtil.printStackTrace(e);
				return searchId;
			}			
			finally{
				try{
				    if(rs != null){
				        rs.close();
				    }
				    if(stmt != null){
				        stmt.close();
				    }
				}catch(SQLException e){};
			}
			if (IaaDebug.DatabaseDebug) IaaUtil.log(sThreadName + "Get search id " + searchId);		
			return searchId;
	    }catch(Exception e){
	        IaaUtil.printStackTrace(e);
	    }finally {
            try {
                if (cn != null)
                    cn.close();
            } catch (SQLException e) {
            }
        }
	    return -1 ;
	}
		
	public  void updateRequestStartTime(int searchId,Timestamp processStartTime, String sThreadName) {
	    Connection cn = null ;
	    try{
	        cn=pool.getConnection();
			PreparedStatement pStmt = null;
			try  {
				pStmt = cn.prepareStatement(
					"update  tblSearch set dteStartTime = CURRENT_TIMESTAMP where intSearchID = ?"); 
				//pStmt.setTimestamp(1,processStartTime);
				pStmt.setInt( 1, searchId);
	
				pStmt.executeUpdate();
			} catch (SQLException e)  {
			    reGetConnection();
				IaaUtil.log(sThreadName + "SQL7 Error update tblSearch " + e);
			    IaaUtil.printStackTrace(e);
				return;
			}
			finally{
				try{
				    if(pStmt != null){
				        pStmt.close();
				    }
				}catch(SQLException e){};
			}
			if (IaaDebug.DatabaseDebug) IaaUtil.log(sThreadName + "Update search start time " + searchId);		
			return;
	    }catch(Exception e){
	        IaaUtil.printStackTrace(e);
	    }finally {
            try {
                if (cn != null)
                    cn.close();
            } catch (SQLException e) {
            }
        }
	}
	
	public  void updateRequestEndTime(int searchId,Timestamp processEndTime, String sThreadName) {
	    Connection cn = null ;
	    try{
	        cn=pool.getConnection();
		    PreparedStatement pStmt = null;
			try  {
				pStmt = cn.prepareStatement(
					"update  tblSearch set dteEndTime = CURRENT_TIMESTAMP where intSearchID = ?"); 
				//pStmt.setTimestamp(1,processEndTime);
				pStmt.setInt( 1, searchId);
	
				pStmt.executeUpdate();
			} catch (SQLException e)  {
			    reGetConnection();
				IaaUtil.log(sThreadName + "SQL7 Error update tblSearch " + e);
			    IaaUtil.printStackTrace(e);
				return;
			}
			finally{
				try{
				    if(pStmt != null){
				        pStmt.close();
				    }
				}catch(SQLException e){};
			}
			if (IaaDebug.DatabaseDebug) IaaUtil.log(sThreadName + "Update search end time " + searchId);		
			return;
	    }catch(Exception e){
	        IaaUtil.printStackTrace(e);
	    }finally {
            try {
                if (cn != null)
                    cn.close();
            } catch (SQLException e) {
            }
        }
	}

	public  void updateRequestSpiderName(int searchId,String spiderName) {
	    Connection cn = null ;
	    try{
	        cn=pool.getConnection();
			PreparedStatement pStmt = null;
			
			try  {
				pStmt = cn.prepareStatement(
					"update  tblSearch set strSpiderName = ? where intSearchID = ?"); 
				pStmt.setString(1,spiderName);
				pStmt.setInt( 2, searchId);
	
				pStmt.executeUpdate();
			} catch (SQLException e)  {
			    reGetConnection();
				IaaUtil.log(spiderName + "SQL7 Error update tblSearch " + e);
			    IaaUtil.printStackTrace(e);
				return;
			}
			finally{
				try{
				    if(pStmt != null){
				        pStmt.close();
				    }
				}catch(SQLException e){};
			}
			if (IaaDebug.DatabaseDebug) IaaUtil.log(spiderName + "Update search spider name " + searchId);		
			return;
	    }catch(Exception e){
	        IaaUtil.printStackTrace(e);
	    }finally {
            try {
                if (cn != null)
                    cn.close();
            } catch (SQLException e) {
            }
        }
	}
		
	public  void updateRequestState(int searchId,int searchState, String sThreadName) {
	    Connection cn = null ;
	    try{
	        cn=pool.getConnection();
			PreparedStatement pStmt = null;
			try  {
				pStmt = cn.prepareStatement(
					"update  tblSearch set intState = ? , dteEndTime = CURRENT_TIMESTAMP where intSearchID = ?"); 
				pStmt.setInt(1,searchState);
				pStmt.setInt( 2, searchId);
	
				pStmt.executeUpdate();
			} catch (SQLException e)  {
			    reGetConnection();
				IaaUtil.log(sThreadName + "SQL7 Error update tblSearch " + e);
			    IaaUtil.printStackTrace(e);
				return;
			}
			finally{
				try{
				    if(pStmt != null){
				        pStmt.close();
				    }
				}catch(SQLException e){};
			}
			if (IaaDebug.DatabaseDebug) IaaUtil.log(sThreadName + "Update search state " + searchId);		
			return;
	    }catch(Exception e){
	        IaaUtil.printStackTrace(e);
	    }finally {
            try {
                if (cn != null)
                    cn.close();
            } catch (SQLException e) {
            }
        }
	}

	/*
	 * This method has been modify by ShengMeng Bai at 2004-07-30
	 */
//	private synchronized int storeImage(AdImage image, String spiderName)
	private  int storeAdData(AdData objData, String sSpiderName,Connection cn)
		throws RemoteException {
		String sAdType = objData.getDataType();
		if (IaaDebug.DatabaseDebug) 
			IaaUtil.log( sSpiderName + "Start store " + sAdType + " data" );		
		PreparedStatement objPrStmt = null;
		ResultSet rs = null;
		int iImageId = -1;
		byte[] bytSig = null;
		if(objData != null){//added by yxy 2008-10-27
			bytSig = objData.getSignature();
		}
		if (bytSig != null)  {
			try  {
				objPrStmt = cn.prepareStatement("SELECT intImageID from  tblImage" 
								+ " where binSignature = ?"); 
				objPrStmt.setBytes(1, bytSig);
				rs = objPrStmt.executeQuery();
				if (rs.next())  {
					iImageId = rs.getInt(1);
				} 
			} catch (SQLException e)  {
			    reGetConnection();
				IaaUtil.log(sSpiderName + "SQL7 Error tblImage select " + e);
			    IaaUtil.printStackTrace(e);
				return iImageId;
			}
			finally{
				try{
				    if(rs != null){
				        rs.close();
				        rs = null;
				    }
				    if(objPrStmt != null){
				        objPrStmt.close();
				        objPrStmt = null;
				    }
				}catch(SQLException e){};
			}
		}
		
		if (iImageId == -1 && objData.getWidth() > 0 && objData.getHeight() > 0)  {//updated by yxy 2008-10-27
			try  {
				objPrStmt = cn.prepareStatement("insert into  tblImage " +
						"(binSignature, strCodeBase, strImageType, intImageSize, " + 
						"intImageHeight, intImageWidth ) Values (?,?,?,?,?,?)"); 
			
				objPrStmt.setBytes(1, bytSig);
				String sCodeBase = objData.getCodeBase();
				if (sCodeBase != null){
					objPrStmt.setString( 2, sCodeBase );
				} else {
					objPrStmt.setNull( 2, Types.VARCHAR );
				}
				objPrStmt.setString( 3, objData.getDataType());
				objPrStmt.setInt(4, sizeToSave(objData.getDataSize()));
				objPrStmt.setInt(5, sizeToSave(objData.getHeight()));
				objPrStmt.setInt(6, sizeToSave(objData.getWidth()));
		
				objPrStmt.executeUpdate();
			} catch (SQLException e)  {
			    reGetConnection();
				IaaUtil.log(sSpiderName +	"SQL7 Error tblImage " + e);
			    IaaUtil.printStackTrace(e);
				return iImageId;
			}
			finally{
				try{
				    if(objPrStmt != null){
				        objPrStmt.close();
				        objPrStmt = null;
				    }
				}catch(SQLException e){};
			}
				
			if(IaaDebug.DatabaseDebug) 
				IaaUtil.log( sSpiderName + "Finish store tblImage" );		
						
			Statement objStmt = null;
			try  {
				objStmt= cn.createStatement();
				rs = objStmt.executeQuery("SELECT @@IDENTITY");
				if (rs.next())  {
					iImageId = rs.getInt(1);
				} else  {
					IaaUtil.log(sSpiderName + "SQL7 cannot get the image id.");			
				}
			} catch (SQLException e)  {
			    reGetConnection();
				IaaUtil.log(sSpiderName + "SQL7 cannot get the image id " + e);
			    IaaUtil.printStackTrace(e);
				return iImageId;
			}
			finally{
				try{
				    if(rs != null){
				        rs.close();
				        rs = null;
				    }
				    if(objStmt != null){
				        objStmt.close();
				        objStmt = null;
				    }
				}catch(SQLException e){};
			}
				
			if(IaaDebug.DatabaseDebug) 
				IaaUtil.log( sSpiderName + 
					"Finish getting imageId " + iImageId);	
			//insert the image data into tblImageData
			insertImageData(iImageId, objData, sSpiderName,cn);
		}
		if (IaaDebug.DatabaseDebug) 
			IaaUtil.log( sSpiderName + "End store " + sAdType + " data" );		
		return iImageId; 	
	}
	
	private boolean insertImageData(int iImageId, AdData objData, String sSpiderName,Connection cn){
		PreparedStatement objPrStmt = null;
		try  {
			objPrStmt = cn.prepareStatement("insert into  tblImageData " + 
				"(intImageID, imgImage) Values (?,?)");
			InputStream in = objData.getData();
			if (in != null)  {
				objPrStmt.setInt(1,iImageId);
				objPrStmt.setBinaryStream(2,in, objData.getDataSize());
				objPrStmt.executeUpdate();
				in.close();
			} else  {
				if(IaaDebug.DatabaseDebug)
					IaaUtil.log(sSpiderName + "No image data in AdDataLink object");
				return false;
			}
		} catch (SQLException e)  {
		    reGetConnection();
			IaaUtil.log(sSpiderName + "SQL7 Error tblImageData " + e);
		    IaaUtil.printStackTrace(e);
		    IaaUtil.log("Image size was "+objData.getDataSize());
		    IaaUtil.log("Image ID is " +iImageId);
		    IaaUtil.log("Spider name is " +sSpiderName);
			return false;	
		} catch (IOException e)  {
			IaaUtil.log(sSpiderName + "Close image stream error " + e);
		    IaaUtil.printStackTrace(e);
			return false;	
		} 
		finally{
			try{
			    if(objPrStmt != null){
			        objPrStmt.close();
			    }
			}catch(SQLException e){};
		}
			
		if(IaaDebug.DatabaseDebug) 
			IaaUtil.log( sSpiderName + 
				"Finish store tblImageData " + iImageId);							
		return true;
	}

	public  LinkedList loadCustomerRequest(String sSpiderName) throws RemoteException {
	    Connection cn = null ;
	    try{
	        cn=pool.getConnection();

		    LinkedList lReqs = new LinkedList();
	
			Statement stmt = null;
			ResultSet rs = null;
			try  {
				stmt = cn.createStatement();
				//Add "ORDER BY intRequestID ASC" to the following SQL statement
				//in order to meet the IMS new requirement
				rs = stmt.executeQuery("select * from tblRequest " + 
				        "where bitLoadFlag = 0 ORDER BY intRequestID ASC");
				while(rs.next()) {
					int requestId = rs.getInt("intRequestID");
					int customerId = rs.getInt("intCustomerID");
					String siteUrl = rs.getString("strSiteURL");
					int searchDepth = rs.getInt("intSearchDepth");
					String samplingMethod = rs.getString("strSamplingMethod");
					int maxRunningTime = rs.getInt("intMaxRunningTime");
					String httpMethod = rs.getString("strHTTPMethod");
					String userAgent = rs.getString("strUserAgent");
					//++Daniel B.  Jan/15/07 - for max impressions
					int maxImpressions = rs.getInt("maxImpressions");
					//add by ShengMengBai at 2005-04-29
					boolean bRandomUserAgent = rs.getBoolean("bitRandomUserAgent");
					boolean bObeyRobotsTXT = rs.getBoolean("bitObeyRobotsTXT");
					
	//++changsheng 2006-5-18
					int intSiteCounterMax = rs.getInt("counterMax");
	//--
					
					//compose the LocalRequest object 
					LocalRequest lReq = new LocalRequest(siteUrl,searchDepth);
					lReq.setSpiderDB(this);
					//++Daniel B.  Jan/15/07 - for max impressions
//++c.s
					if(maxImpressions > 0 )
					lReq.setMaxImpressions(maxImpressions);
//--
					lReq.setRequestId(requestId);
					lReq.setCustomerId(customerId);
					lReq.setMaxRunningTime(maxRunningTime);
					lReq.setSamplingMethod(samplingMethod);
					
					UserAgentAux agentAux = null;
					//++ change by ShengMengBai at 2005-04-29
					//based on the setting, get the UserAgentAux object.
					if(bRandomUserAgent){
					    agentAux = UserAgentAux.getInstance();
					}else{
					    if(userAgent == null){
					        userAgent = "Mozilla/4.0 (compatible; MSIE 5.0; Windows NT 4.0)";
					    }
					    agentAux = UserAgentAux.getInstance(userAgent);
					}
					lReq.setUserAgentObject(agentAux);
					lReq.setObeyRobotsTXT(bObeyRobotsTXT);
					//delete old 
					//lReq.setUserAgent(userAgent);
					//-- change by ShengMengBai at 2005-04-29
					
					RequestParameters params = null;
					if (!(httpMethod.toUpperCase()).equals("NONE")) {
						if(IaaDebug.Debug) IaaUtil.log(sSpiderName + "Try to get params");					
						params = loadRequestKeyword(requestId, httpMethod, sSpiderName,cn);
						lReq.setRequestParams(params);
						//note lReq will be modified
						setRequestParams(lReq, sSpiderName,cn);
					}
					
					lReq.setSpecialJS(loadRequestSpecialJS(requestId,sSpiderName,cn));//changsheng 2006-4-30
					
					//add the object into the LinkedList
					lReqs.addLast(lReq);
					PreparedStatement pStmt = cn.prepareStatement("update " +
									"tblRequest set bitLoadFlag = 1 " +
									"where intRequestID = ?");
					pStmt.setInt(1, requestId);
					pStmt.executeUpdate();
				}
			} catch (SQLException e)  {
			    reGetConnection();
				IaaUtil.log(sSpiderName + "SQL7 Error [REQUEST] " + e);		
			    IaaUtil.printStackTrace(e);
				throw new RemoteException();
			}
			finally{
				try{
				    if(rs != null){
				        rs.close();
				    }
				    if(stmt != null){
				        stmt.close();
				    }
				}catch(SQLException e){};
			}
			return lReqs;
	    }catch(Exception e){
	        IaaUtil.printStackTrace(e);
	    }finally {
            try {
                if (cn != null)
                    cn.close();
            } catch (SQLException e) {
            }
        }
	    return new LinkedList() ;
	}	
	
	/**
	 * Get the agency information from database,
	 * the infomation is used to identify whether 
	 * the advertisement is agency.
	 * @author ShengMeng Bai
	 * @return LinkedList which saving the objects of Agency class.
	 */
	public  LinkedList loadAgency(String sSpiderName, int intRequestID) throws RemoteException {
	    Connection cn = null ;
	    try{
	        cn=pool.getConnection();
	        
			LinkedList lstAgencies = new LinkedList();
				
			Statement stmt = null;
			ResultSet rs = null;
			try  {
				stmt = cn.createStatement();
				rs = stmt.executeQuery("" +
						"select intAgencyID, strURLKeyword from tblAgency" +
						" where intAgencyID NOT IN " +
						" (SELECT intAgencyID FROM tblAgenciesFilter " +
						" WHERE intRequestID = " + intRequestID + ")");
				
				while(rs.next()) {
					int iAgencyId = rs.getInt("intAgencyID");
					String sKeywords = rs.getString("strURLKeyword");
					
					AdAgency objAgency = new AdAgency(iAgencyId, sKeywords);
					//add the object into the LinkedList
					lstAgencies.addLast(objAgency);
				}
			} catch (SQLException e)  {
			    reGetConnection();
				IaaUtil.log(sSpiderName + "SQL7 Error [GET AGENCY] " + e);		
			    IaaUtil.printStackTrace(e);
				throw new RemoteException();
			}
			finally{
				try{
				    if(rs != null){
				        rs.close();
				    }
				    if(stmt != null){
				        stmt.close();
				    }
				}catch(SQLException e){};
			}
			return lstAgencies;
	    }catch(Exception e){
	        IaaUtil.printStackTrace(e);
	    }finally {
            try {
                if (cn != null)
                    cn.close();
            } catch (SQLException e) {
            }
        }
	    return new LinkedList();
	}	
	//	added by yxy 2008-05-14, for intCriteria calculation	
	public  LinkedList loadAdSize(String sSpiderName, int intRequestID) throws RemoteException {
	    Connection cn = null ;
	    try{
	        cn=pool.getConnection();
	        
			LinkedList lstAdSize = new LinkedList();
				
			Statement stmt = null;
			ResultSet rs = null;
			try  {
				stmt = cn.createStatement();
				rs = stmt.executeQuery("" +
						"select intSizeID, width,height,score from tblAdSize" );
				
				while(rs.next()) {
					int intSizeID = rs.getInt("intSizeID");
					int width = rs.getInt("width");
					int height = rs.getInt("height");
					int score = rs.getInt("score");
					AdSize objAdSize = new AdSize(intSizeID,width,height,score);
					//add the object into the LinkedList
					lstAdSize.addLast(objAdSize);
				}
			} catch (SQLException e)  {
			    reGetConnection();
				IaaUtil.log(sSpiderName + "SQL7 Error [GET AGENCY] " + e);		
			    IaaUtil.printStackTrace(e);
				throw new RemoteException();
			}
			finally{
				try{
				    if(rs != null){
				        rs.close();
				    }
				    if(stmt != null){
				        stmt.close();
				    }
				}catch(SQLException e){};
			}
			return lstAdSize;
	    }catch(Exception e){
	        IaaUtil.printStackTrace(e);
	    }finally {
            try {
                if (cn != null)
                    cn.close();
            } catch (SQLException e) {
            }
        }
	    return new LinkedList();
	}

	private  RequestParameters loadRequestKeyword(int requestId,String httpMethod, String sSpiderName,Connection cn) {
		RequestParameters params = new RequestParameters();
		if ((httpMethod.toUpperCase()).equals("GET"))  {
			params.setUseGetMethod();
		} else  {
			if ((httpMethod.toUpperCase()).equals("POST"))  {
				params.setUsePostMethod();
			} else  {
				IaaUtil.log(sSpiderName + "HTTP method Error");
				return params;
			}
		}
			
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try  {
			pStmt = cn.prepareStatement(
					"select * from tblRequestKeyword where intRequestID = ?");
			pStmt.setInt(1, requestId);
			
			rs = pStmt.executeQuery();
			int paramsCount = 0;
			while(rs.next()) {
				String keyword = rs.getString("strSearchKeyword");
				params.put(new Integer(paramsCount).toString(), keyword);
				if (IaaDebug.DatabaseDebug)	 {
					IaaUtil.log(sSpiderName + "Put(\"" + paramsCount  +"\" , \"" +  
							keyword + "\");" );
				}
				paramsCount++;				
			}
		} catch (SQLException e)  {
		    reGetConnection();
			IaaUtil.log(sSpiderName + "SQL7 Error [REQUESTKEYWORD] " + e);		
		    IaaUtil.printStackTrace(e);
		}
		finally{
			try{
			    if(rs != null){
			        rs.close();
			    }
			    if(pStmt != null){
			        pStmt.close();
			    }
			}catch(SQLException e){};
		}
		return params;
	}		
	
	private  void setRequestParams(LocalRequest lReq, String sSpiderName,Connection cn) {
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try  {
			pStmt = cn.prepareStatement(
					"select * from tblRequestParams where intRequestID = ?");
			pStmt.setInt(1, lReq.getRequestId());
			
			rs = pStmt.executeQuery();
			if(rs.next()) {
				String startParams = rs.getString("strStartParams");
				String endParams = rs.getString("strEndParams");
				lReq.setStartParams(startParams);
				lReq.setEndParams(endParams);

			}
		} catch (SQLException e)  {
		    reGetConnection();
			IaaUtil.log(sSpiderName + "SQL7 Error [REQUESTPARAMS] " + e);		
		    IaaUtil.printStackTrace(e);
		}
		finally{
			try{
			    if(rs != null){
			        rs.close();
			    }
			    if(pStmt != null){
			        pStmt.close();
			    }
			}catch(SQLException e){};
		}
	}
	/**
	 * Some impression state isn't successful, 
	 * so, need re-connect to the resouce, call this method
	 * to the the bad impression.
	 * @param iMaxTimes, the max retry times, if the times lagger than iMaxTimes, will don't get the data
	 * @param iInterval, the retry time interval, unit is hours, 
	 * 
	 * @return AdDataLink object list, 
	 * 			this AdDataLink object has been initial, can be call parse(List) method directly.
	 */
	public  List getReConnectImp(int iMaxTimes, int iInterval, String sSpiderName){
	    Connection cn = null ;
	    try{
	        cn=pool.getConnection();

			LinkedList lstImp = new LinkedList();
			Statement stmt = null;
			ResultSet rs = null;
			try  {
				
				long lTime = System.currentTimeMillis() - iInterval * 3600000;
				Timestamp time = new Timestamp(lTime);
				String sSQL = "EXECUTE RetryImpression '" + time + "', " + iMaxTimes;
				stmt = cn.createStatement();
				IaaUtil.log(sSpiderName + "Get Reconnect Impression: " + sSQL);
				rs = stmt.executeQuery(sSQL);
				while(rs.next()) {
					int iImpressionId = rs.getInt(1);
					int iImageId = rs.getInt(2);
					String sImageURL = rs.getString(3);
					URL url = null;
					try{
						url = URLUtil.getURL(sImageURL);
					}catch(java.net.MalformedURLException e){
						//it impossilbe
						continue;
					}
					String sType = rs.getString(4);
					String sUserAgent = rs.getString(5);
					if(sImageURL != null){
						AdDataLink objAD = null;
						if(sType.indexOf(AdDataLink.ADTYPE_IMAGE) > -1){
							objAD = new ImgLink(url, iImpressionId, iImageId, sUserAgent);
						}else if(sType.indexOf(AdDataLink.ADTYPE_FLASH) > -1){
							objAD = new FlashLink(url, iImpressionId, iImageId, sUserAgent);
						}else if(sType.indexOf(AdDataLink.ADTYPE_APPLET) > -1){
							objAD = new AppletLink(url, iImpressionId, iImageId, sUserAgent);
						}else{
							IaaUtil.log(sSpiderName + "Fatal error, the AdType is: " + sType);
							continue;
						}
						lstImp.addLast(objAD);
					}
				}
			} catch (SQLException e){
			    reGetConnection();
				IaaUtil.log(sSpiderName + "SQL Error [EXEC RetryImpression] " + e);		
			    IaaUtil.printStackTrace(e);
			}
			finally{
				try{
				    if(rs != null){
				        rs.close();
				    }
				    if(stmt != null){
				        stmt.close();
				        stmt = null;
				    }
				}catch(SQLException e){};
			}
			return lstImp;
	    }catch(Exception e){
	        IaaUtil.printStackTrace(e);
	    }finally {
            try {
                if (cn != null)
                    cn.close();
            } catch (SQLException e) {
            }
        }
	    return new LinkedList() ;
	}
	private  void updateAdData(AdData objData, int iImageId, String sThreadName,Connection cn) throws RemoteException {
		if (iImageId > 0)  {
			String sAdType = objData.getDataType();
			if (IaaDebug.DatabaseDebug) 
				IaaUtil.log(sThreadName + "Start update AdData " + iImageId);		

			PreparedStatement objPrStmt = null;
		
			try{
				objPrStmt = cn.prepareStatement("UPDATE tblImage " +
						"SET binSignature=?, strImageType=?, intImageSize=?, " + 
						"intImageHeight=?, intImageWidth=? " + 
						"WHERE intImageID=" + iImageId); 
			
				objPrStmt.setBytes(1, objData.getSignature());
				objPrStmt.setString(2, objData.getDataType());
				objPrStmt.setInt(3, sizeToSave(objData.getDataSize()));
				objPrStmt.setInt(4, sizeToSave(objData.getHeight()));
				objPrStmt.setInt(5, sizeToSave(objData.getWidth()));
		
				objPrStmt.executeUpdate();
			} catch (SQLException e)  {
			    reGetConnection();
				IaaUtil.log(sThreadName + "SQL Error tblImage " + e);
			    IaaUtil.printStackTrace(e);
				return;
			}
			finally{
				try{
				    if(objPrStmt != null){
				        objPrStmt.close();
				        objPrStmt = null;
				    }
				}catch(SQLException e){};
			}
				
			if(IaaDebug.DatabaseDebug) 
				IaaUtil.log(sThreadName + "Finish update tblImage" );		
	
			if(IaaDebug.DatabaseDebug) 
				IaaUtil.log(sThreadName + "Start update tblImageData " + iImageId);							
			try  {
				objPrStmt = cn.prepareStatement("UPDATE tblImageData " + 
					"SET imgImage=? WHERE intImageID=" + iImageId); 
				InputStream in = objData.getData();
				if (in != null)  {
					objPrStmt.setBinaryStream(1,in, objData.getDataSize());
					int iCount = objPrStmt.executeUpdate();
					//if there are not data in tblImageData table, we will insert it to the database.
					if(iCount == 0){
						if(IaaDebug.DatabaseDebug) 
							IaaUtil.log(sThreadName + "There is not intImageID=" + iImageId + ", will inset it.");		
						//insert the image data into tblImageData
						insertImageData(iImageId, objData, sThreadName,cn);
					}
					in.close();
				} else  {
					if(IaaDebug.DatabaseDebug)
						IaaUtil.log(sThreadName + "No image data in AdDataLink object");
				}
			} catch (SQLException e)  {
			    reGetConnection();
				IaaUtil.log(sThreadName + "SQL7 Error tblImageData " + e);
			    IaaUtil.printStackTrace(e);
				return;	
			} catch (IOException e)  {
				IaaUtil.log(sThreadName + "Close image stream error " + e);
			    IaaUtil.printStackTrace(e);
				return;	
			} 
			finally{
				try{
				    if(objPrStmt != null){
				        objPrStmt.close();
				    }
				}catch(SQLException e){};
			}
			
			if(IaaDebug.DatabaseDebug) 
				IaaUtil.log(sThreadName + "Finish update tblImageData " + iImageId);							
		} else  {
			IaaUtil.log(sThreadName + "intImageID is invalid, Can't update the tblImage table, intImageID equal " + iImageId);		
		}
	}
	/**
	 * After re-connect the resouce, we must through this method update the database
	 * @see #getReConnectImp(int, int)
	 * @param objAdDataLink AdDataLink object, the object's m_iOldImpressionID and m_iOldImageID must > 0
	 * @throws RemoteException
	 */
	public  void updateAdDataLink(AdDataLink objAdDataLink, String sThreadName) throws RemoteException {
	    Connection cn = null ;
	    try{
	        cn=pool.getConnection();

		    int iImpressionID = objAdDataLink.getOldImpressionID();
			//if the id is invalid, return
			if(iImpressionID < 0){
				IaaUtil.log(sThreadName + "intImpressionID is invalid, Can't update the tblImpression table, intImageID equal " + iImpressionID);		
				return;
			}
			int iImageId = objAdDataLink.getOldImageID();
			
			//if get re-connect ad successfully, save the data
			if(objAdDataLink.isConnectSuccessful()){
				try  {
					updateAdData(objAdDataLink.getAdData(), iImageId, sThreadName,cn);
				} catch (RemoteException e)  {
					IaaUtil.printStackTrace(e);
				}
			}else{
				if(IaaDebug.DatabaseDebug) 
				    IaaUtil.log(sThreadName + "can't get the image data, don't update tblImageData table.");		
			}
	
			PreparedStatement objPrStmt = null;
			try  {		
				objPrStmt = cn.prepareStatement("UPDATE tblImpression " +
					"SET intImpressionHeight=?, intImpressionWidth=?, " +
					"strAdvertisementTarget=?, intAgencyID=?, bitState=?, intRetryTimes=intRetryTimes+1, " +
					"dteCollectTime=CURRENT_TIMESTAMP WHERE intImpressionID=" + iImpressionID); 
		
				objPrStmt.setInt(1, sizeToSave(objAdDataLink.getHeight()));		 
				objPrStmt.setInt(2, sizeToSave(objAdDataLink.getWidth()));		 
				String sRealTarget = objAdDataLink.getRealTargetURLStr();
				//if(realTarget.length()>255)realTarget = realTarget.substring(0,255);		 		 
				objPrStmt.setString(3, sRealTarget); 
				if (objAdDataLink.getAdAgency()!= null){
					objPrStmt.setInt(4, objAdDataLink.getAdAgency().getAgencyID());
				} else {
					objPrStmt.setNull(4, java.sql.Types.INTEGER);
				}
				if(objAdDataLink.isConnectSuccessful()){
					objPrStmt.setInt(5, 1);
				}else{
					objPrStmt.setInt(5, 0);
				}
		
				objPrStmt.executeUpdate();
			} catch (SQLException e)  {
			    reGetConnection();
				IaaUtil.printStackTrace(e);
			}		
			finally{
				try{
				    if(objPrStmt != null){
				        objPrStmt.close();
				    }
				}catch(SQLException e){};
			}
			if(IaaDebug.DatabaseDebug) 
				IaaUtil.log(sThreadName + "Finish update AdDataLink object");	
	    }catch(Exception e){
	        IaaUtil.printStackTrace(e);
	    }finally {
            try {
                if (cn != null)
                    cn.close();
            } catch (SQLException e) {
            }
        }
	    return  ;
	}

    /* (non-Javadoc)
     * @see com.ims.iaa.SpiderDBConnection#getUnparsedFlashImp(String sThreadName)
     */
    public List getUnparsedFlashImp(String sThreadName) throws RemoteException {
        Connection cn = null ;
	    try{
	        cn=pool.getConnection();
			LinkedList lstImp = new LinkedList();
			Statement stmt = null;
			ResultSet rs = null;
			try  {
				String sSQL = "EXECUTE GetUnparsedFlash";
				stmt = cn.createStatement();
				IaaUtil.log(sThreadName + "Get the flash data to get the URL: " + sSQL);
				rs = stmt.executeQuery(sSQL);
				HashMap mapImage = new HashMap();
				while(rs.next()) {
					int iImpressionId = rs.getInt("intImpressionID");
					int iImageId = rs.getInt("intImageID");
					String sImageURL = rs.getString("strImageURL");
					String sPublisher = rs.getString("strPublisherURL");
					URL urlContainer = null;
					URL urlImage = null;
					try{
					    urlContainer = URLUtil.getURL(sPublisher);
					    urlImage = URLUtil.getURL(sImageURL);
					}catch(java.net.MalformedURLException e){
						//it impossilbe
						continue;
					}
					String sUserAgent = rs.getString("strUserAgent");
					//++ if the flash had been get, don't reget it.
					AdFlash flash = null;
					Integer intID = new Integer(iImageId);
					if(!mapImage.containsKey(intID)){
						//get the flash size, if the size < 0, don't process it
						int iLen = rs.getInt("intImageSize");
						if(iLen <= 0){
						    continue;
						}
						byte[] bytImage = new byte[iLen + 1024];
						//get the data form InputSream
						InputStream in = rs.getBinaryStream("imgImage");
						if(in == null){
						    continue;
						}
						//read the flash data form input stream
						try{
						    int iStart = 0;
						    iLen = in.read(bytImage);
							while(iLen > 0 && iLen < bytImage.length){
							    iStart += iLen;
							    iLen = in.read(bytImage, iLen, bytImage.length - iStart);
							}
							iLen = iStart;
							in.close();
						}catch(IOException e){
						    reGetConnection();
							IaaUtil.log(sThreadName + "get the Flash Data error: " + e);		
						    IaaUtil.printStackTrace(e);
						    continue;
						}
						flash = new AdFlash();
						flash.setData(bytImage);
						flash.setDataSize(iLen);
						String sFlashURL = rs.getString("flashURL");
						String sFlashURLVar = rs.getString("flashURLVar");
						flash.setFlashURL(sFlashURL);
						flash.setFlashURLVar(sFlashURLVar);
						mapImage.put(intID, flash); 
					}else{
					    flash = (AdFlash)mapImage.get(intID);
					}
					//-- if the flash had been get, don't reget it.
					FlashLink objAD = new FlashLink(urlContainer, urlImage, iImpressionId, iImageId, sUserAgent, flash);
					lstImp.addLast(objAD);
				}
			} catch (SQLException e){
			    reGetConnection();
				IaaUtil.log(sThreadName + "SQL Error [EXEC RetryImpression] " + e);		
			    IaaUtil.printStackTrace(e);
			}
			finally{
				try{
				    if(rs != null){
				        rs.close();
				    }
				    if(stmt != null){
				        stmt.close();
				        stmt = null;
				    }
				}catch(SQLException e){};
			}
			return lstImp;
		
	    }catch(Exception e){
	        IaaUtil.printStackTrace(e);
	    }finally {
            try {
                if (cn != null)
                    cn.close();
            } catch (SQLException e) {
            }
        }
	    return  new LinkedList();
    }
	private  void updateFlashDataURL(AdFlash flash, int iImageId, String sThreadName,Connection cn) throws RemoteException {
	    if (IaaDebug.DatabaseDebug) 
			IaaUtil.log(sThreadName + "Start update Flash URL " + iImageId);		

		PreparedStatement objPrStmt = null;
	
		try{
			objPrStmt = cn.prepareStatement("UPDATE tblImage " +
					"SET flashURL=?, flashURLVar=?" + 
					" WHERE intImageID=" + iImageId); 
		
			objPrStmt.setString(1, flash.getFlashURL());
			objPrStmt.setString(2, flash.getFlashURLVar());
	
			objPrStmt.executeUpdate();
		} catch (SQLException e)  {
		    reGetConnection();
			IaaUtil.log(sThreadName + "SQL Error tblImage " + e);
		    IaaUtil.printStackTrace(e);
			return;
		}
		finally{
			try{
				    if(objPrStmt != null){
				        objPrStmt.close();
				        objPrStmt = null;
				    }
				}catch(SQLException e){};
			}
	}
	/**
	 * After re-connect the resouce, we must through this method update the database
	 * @see #getReConnectImp(int, int)
	 * @param objAdDataLink AdDataLink object, the object's m_iOldImpressionID and m_iOldImageID must > 0
	 * @throws RemoteException
	 */
	public  void updateFlashURL(FlashLink flashLink, String sThreadName) throws RemoteException {
	    Connection cn = null ;
	    try{
	        cn=pool.getConnection();
	        
			int iImpressionID = flashLink.getOldImpressionID();
			//if the id is invalid, return
			if(iImpressionID < 0){
				IaaUtil.log(sThreadName + "intImpressionID is invalid, Can't update the tblImpression table, intImageID equal " + iImpressionID);		
				return;
			}
			//update the tlbImage
			AdFlash flash = (AdFlash)flashLink.getAdData();
			int iImageID = flashLink.getOldImageID();
			if(iImageID > -1){
			    updateFlashDataURL(flash, iImageID, sThreadName,cn);
			}
	
			String sAdvertise = flashLink.getAdvertiser();
			int iAdvertiserId = -1;
			if ( sAdvertise != null ){
				iAdvertiserId = getCompanyIdFromDB( flashLink.getAdvertiser(), sThreadName,cn);
			}
					
			PreparedStatement objPrStmt = null;
			try  {		
				objPrStmt = cn.prepareStatement("UPDATE tblImpression " +
					"SET strIMGTargetURL=?, strAdvertisementTarget=?, " +
					"intAdvertiserID=? WHERE intImpressionID=" + iImpressionID); 
				//because we had parse the URL, if can't parse fail, will set the URL to ""
				//but if target same as real target, we don't save target
				String sTarget = flashLink.getTargetURLStr();
				String sRealTarget = flashLink.getRealTargetURLStr();
				if(sTarget == null){
				    sTarget = "";
				}else if(sRealTarget != null && sRealTarget.equals(sTarget)){
				    sTarget = "";
				}
				objPrStmt.setString(1, sTarget);
				objPrStmt.setString(2, sRealTarget);		 
				if (iAdvertiserId != -1){
					objPrStmt.setInt(3, iAdvertiserId);
				} else {
					objPrStmt.setNull(3, java.sql.Types.INTEGER);
				}
		
				objPrStmt.executeUpdate();
			} catch (SQLException e)  {
			    reGetConnection();
				IaaUtil.printStackTrace(e);
			}		
			finally{
				try{
				    if(objPrStmt != null){
				        objPrStmt.close();
				    }
				}catch(SQLException e){};
			}
			if(IaaDebug.DatabaseDebug) 
				IaaUtil.log(sThreadName + "Finish update FlashLink object.");		
	    }catch(Exception e){
	        IaaUtil.printStackTrace(e);
	    }finally {
            try {
                if (cn != null)
                    cn.close();
            } catch (SQLException e) {
            }
        }
	}
	private int sizeToSave(int iSize){
	    if(iSize < 0){
	        return 0;
	    }
	    return iSize;
	}	
	//test purpose only
	public static void main(String args[])
	{
		System.out.println("Trying");
		SpiderDBConnectionImpl sp = (SpiderDBConnectionImpl)getInstance();
		//sp.createConnection();
		System.out.println("Trying");
		try{
			List lis = sp.getReConnectImp(5, 12, Thread.currentThread().getName());
			for(int i = 0; i < lis.size(); i++){
				AdDataLink obj = (AdDataLink)lis.get(i);
				System.out.println(obj.getSrcURLStr());
			}
		} catch (Exception e)  {
			System.out.println("Test main error " + e);
		}
		System.exit(0);
		try  {
			LinkedList lis = sp.loadCustomerRequest(Thread.currentThread().getName());
			for (int i = 0;i<lis.size(); i++)  {
				Object lr = ((LocalRequest)lis.get(i)).clone();
				System.out.println(lr.toString());
				sp.getRequestIdFromDB((LocalRequest)lr, Thread.currentThread().getName());
			}
		} catch (Exception e)  {
			System.out.println("Test main error " + e);
		}
		System.exit(0);
	}

    /* (non-Javadoc)
     * @see com.ims.iaa.SpiderDBConnection#getProxy(java.lang.String, java.lang.String)
     */
    public  ProxySetting getProxy(String sMachineName, String sThreadName) throws RemoteException {
        Connection cn = null ;
	    try{
	        cn=pool.getConnection();
	        
			Statement stmt = null;
			ResultSet rs = null;
			try  {
				String sSQL = "EXECUTE spGetProxyHost '" + sMachineName + "'";
				stmt = cn.createStatement();
				IaaUtil.log(sThreadName + "Get proxy: " + sSQL);
				rs = stmt.executeQuery(sSQL);
				while(rs.next()) {
				    ProxySetting setting = new ProxySetting();
				    setting.setHost(rs.getString(1));
				    setting.setPort(rs.getString(2));
				    setting.setUserName(rs.getString(3));
				    setting.setPassword(rs.getString(4));
				    return setting;
				}
			} catch (SQLException e){
			    reGetConnection();
				IaaUtil.log(sThreadName + "SQL Error [EXEC RetryImpression] " + e);		
			    IaaUtil.printStackTrace(e);
			}
			finally{
				try{
				    if(rs != null){
				        rs.close();
				    }
				    if(stmt != null){
				        stmt.close();
				        stmt = null;
				    }
				}catch(SQLException e){};
			}
			return null;
	    }catch(Exception e){
	        IaaUtil.printStackTrace(e);
	    }finally {
            try {
                if (cn != null)
                    cn.close();
            } catch (SQLException e) {
            }
        }
	    return null ;
    }
    
    //add by morgan to reconnect to the db, when got socket reset error
    private  void reGetConnection(){
        /*IaaUtil.log("Socket error, reConnect to the db");
        if(cn != null){
            try {
                cn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        createConnection();*/
    }

    //++changsheng 2006-5-16
    /*
	public  void saveCacheAdDataLink(ImgLink objAdDataLink, LocalRequest lReq, String sThreadName) throws RemoteException {
	    Connection cn = null ;
	    try{
	        cn=pool.getConnection();
	        
			int iPublisherId = getCompanyIdFromDB( objAdDataLink.getPublisher(), sThreadName,cn);
			
			Statement stmt = null;
			ResultSet rs = null;
			
			String sSQL = "EXECUTE spSaveCacheAdDataLink " 
				+ objAdDataLink.getIntReferImprID() 
				+ "," + lReq.getSearchId()
				+ "," + iPublisherId
				+ ",'" + objAdDataLink.getPublisherURL().toString()+"'" 
				+ "," + objAdDataLink.getDepth() //;//add by changsheng for depth
				+ ",'" + objAdDataLink.getSrcURLStr()+"'" ;//add by changsheng 20006-8-24			

			try  {

				stmt = cn.createStatement();
				stmt.executeUpdate(sSQL);
			} catch (SQLException e){
			    reGetConnection();
				IaaUtil.log(sThreadName + "SQL Error [EXEC spSaveCacheAdDataLink] " + e);		
			    IaaUtil.printStackTrace(e);
			    IaaUtil.log(sSQL);
			}
			finally{
				try{
				    if(rs != null){
				        rs.close();
				    }
				    if(stmt != null){
				        stmt.close();
				        stmt = null;
				    }
				}catch(SQLException e){};
			}
	    }catch(Exception e){
	        IaaUtil.printStackTrace(e);
	    }finally {
            try {
                if (cn != null)
                    cn.close();
            } catch (SQLException e) {
            }
        }
	}*/

	
	
	
	public  void saveCacheAdDataLink(ImgLink objAdDataLink, LocalRequest lReq, String sThreadName) throws RemoteException {
	    Connection cn = null ;
	    try{
	        cn=pool.getConnection();
	        
			int iPublisherId = getCompanyIdFromDB( objAdDataLink.getPublisher(), sThreadName,cn);
			
			PreparedStatement objPrStmt = null;
			ResultSet rs = null;

			try  {
				objPrStmt = cn.prepareStatement("EXECUTE spSaveCacheAdDataLink  ?,?,?,?,?,?");
				objPrStmt.setInt(1, objAdDataLink.getIntReferImprID() );
				objPrStmt.setInt(2, lReq.getSearchId() );
				objPrStmt.setInt(3, iPublisherId );
				objPrStmt.setString(4, objAdDataLink.getPublisherURL().toString());
				objPrStmt.setInt(5, objAdDataLink.getDepth() );
				objPrStmt.setString(6, objAdDataLink.getSrcURLStr());
				objPrStmt.executeUpdate();
			} catch (SQLException e){
			    reGetConnection();
				IaaUtil.log(sThreadName + "SQL Error [EXEC spSaveCacheAdDataLink] " + e);		
			    IaaUtil.printStackTrace(e);
			    
			}
			finally{
				try{
				    if(rs != null){
				        rs.close();
				    }
				    if(objPrStmt != null){
				        objPrStmt.close();
				        objPrStmt = null;
				    }
				}catch(SQLException e){};
			}
	    }catch(Exception e){
	        IaaUtil.printStackTrace(e);
	    }finally {
            try {
                if (cn != null)
                    cn.close();
            } catch (SQLException e) {
            }
        }
	}
	
	
	
	
	/**
     * do the fast compare
     * @return
     */
    public  int doFastCompare(ImgLink ad,LocalRequest lReq,String sThreadName) throws RemoteException
	{
        Connection cn = null ;
	    try{
	        cn=pool.getConnection();
	        
	    	int intRet = 0 ;
	    	CallableStatement st = null;
			try  {
				String sSQL= "{call spDoFastCompare(?,?,?,?)}";  
			    st = cn.prepareCall(sSQL) ;
			    st.setString(1,ad.getOriginalImgURL());   
	            st.setInt(2,IaaDebug.SmartDownloadCounterMax);
	            st.registerOutParameter(3,Types.INTEGER );   
	            st.registerOutParameter(4,Types.INTEGER );   
	            st.execute();  
	            intRet = st.getInt(3);
	            if( intRet == IAppLvl.FCR_MATCH ){
	            	ad.setIntReferInprID(st.getInt(4));
	            	saveCacheAdDataLink(ad,lReq,sThreadName);
	            }
			} catch (SQLException e){
			    reGetConnection();
				IaaUtil.log(sThreadName + "SQL Error [EXEC spDoFastCompare] " + e);		
			    IaaUtil.printStackTrace(e);
			}
			finally{
				try{
				    if(st != null){
				    	st.close();
				    }
				}catch(SQLException e){};
			}
			return intRet ;
	    }catch(Exception e){
	        IaaUtil.printStackTrace(e);
	    }finally {
            try {
                if (cn != null)
                    cn.close();
            } catch (SQLException e) {
            }
        }
	    return 0 ;
	}
    public  int doPreCompare(ImgLink ad,LocalRequest lReq,String sThreadName) throws RemoteException
	{
        Connection cn = null ;
	    try{
	        cn=pool.getConnection(); 
		        
	    	int intRet = 0 ;
	    	CallableStatement st = null;
			try  {
				String sSQL= "{call spDoPreCompare(?,?,?,?,?)}";  
			    st = cn.prepareCall(sSQL) ;
			    st.setString(1,ad.getOriginalImgURL());   
	            st.setInt(2,IaaDebug.SmartDownloadCounterMax);
	            st.registerOutParameter(3,Types.INTEGER );   
	            st.registerOutParameter(4,Types.INTEGER );   
	            if(ad.getAdData()!= null && ad.getAdData().getSignature() != null ){
		            st.setBinaryStream(5,new ByteArrayInputStream(ad.getAdData().getSignature()) , 
							ad.getAdData().getSignature().length);
			    }else{
			        st.setBinaryStream(5,null,0);
			    }
	            st.execute();  
	            intRet = st.getInt(3);
	            if( intRet == IAppLvl.FCR_MATCH ){
	            	ad.setIntReferInprID(st.getInt(4));
	            	saveCacheAdDataLink(ad,lReq,sThreadName);
	            }
			} catch (SQLException e){
			    reGetConnection();
				IaaUtil.log(sThreadName + "SQL Error [EXEC spDoPreCompare] " + e);		
			    IaaUtil.printStackTrace(e);
			}
			finally{
				try{
				    if(st != null){
				    	st.close();
				    }
				}catch(SQLException e){};
			}
			return intRet ;
	    }catch(Exception e){
	        IaaUtil.printStackTrace(e);
	    }finally {
            try {
                if (cn != null)
                    cn.close();
            } catch (SQLException e) {
            }
        }	
	    return 0 ;
	}
    /**
     * do really compare
     * only FCR_INITIAL need exec doCompare
     */
    public  void doCompare(ImgLink ad,int thisImpressionID ,String sThreadName) throws RemoteException 
	{
        Connection cn = null ;
	    try{
	        cn=pool.getConnection();
	        
	    	CallableStatement st = null;
			try  {
				//need parameters:
				//1 srcURL
				//2 signature
				//3 targetURL
				//4 app max counter
				String sSQL= "{call spDoCompare(?,?,?,?,?)}";  
			    st = cn.prepareCall(sSQL) ;
			    st.setString(1,ad.getOriginalImgURL());   
			    //changsheng 2006-9-7 Signature may null 
			    if(ad.getAdData()!= null && ad.getAdData().getSignature() != null ){
		            st.setBinaryStream(2,new ByteArrayInputStream(ad.getAdData().getSignature()) , 
							ad.getAdData().getSignature().length);
			    }else{
			        st.setBinaryStream(2,null,0);
			    }
	            st.setString(3,ad.getRealTargetURLStr());
	            st.setInt(4,IaaDebug.SmartDownloadCounterMax);
	            st.setInt(5,thisImpressionID);
	            st.execute();  
			} catch (SQLException e){
			    reGetConnection();
				IaaUtil.log(sThreadName + "SQL Error [EXEC spDoCompare] " + e);		
			    IaaUtil.printStackTrace(e);
			}
			finally{
				try{
				    if(st != null){
				    	st.close();
				    }
				}catch(SQLException e){};
			}
		    }catch(Exception e){
		        IaaUtil.printStackTrace(e);
		    }finally {
	            try {
	                if (cn != null)
	                    cn.close();
	            } catch (SQLException e) {
	            }
	        }
	}
    
	public  int loadRequestSpecialJS(int requestId, String sSpiderName,Connection cn) {
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		int retSpeicalJS = 0 ;
		try  {
			pStmt = cn.prepareStatement(
					"SELECT intSpecialJS FROM tblRequestSpecialJS where intRequestID = ?");
			pStmt.setInt(1, requestId);
			
			rs = pStmt.executeQuery();
			if(rs.next()) {
				 retSpeicalJS = rs.getInt("intSpecialJS");
			}
		} catch (SQLException e)  {
		    reGetConnection();
			IaaUtil.log(sSpiderName + "SQL7 Error [tblRequestSpecialJS] " + e);		
		    IaaUtil.printStackTrace(e);
		}
		finally{
			try{
			    if(rs != null){
			        rs.close();
			    }
			    if(pStmt != null){
			        pStmt.close();
			    }
			}catch(SQLException e){};
		}
		return retSpeicalJS;
	}
}
